'''
Created on 23/04/2009

@author: eh01
'''
from yarra.datastore.dao import DSODAO, UpdateDAOException,\
    NotFoundDAOException, ConcurrencyDAOException
from yarra.datastore.dso import DSO
from yarra.datastore.dst import DST
import sqlite3

DATABASE_NAME = 'node.db3'

DSO_ID = "dso_id"
DSO_URI = "dso_uri"
DSO_TYPE = "dso_type"
DSO_VERSION = "dso_version"
DSO_CREATED_TIMESTAMP = "dso_created_timestamp"
DSO_ACTIVE = "dso_active"
DST_ID = "dst_id"
DST_SORT_ORDER = "dst_sort_order"
DST_SUBJECT = "dst_subject"
DST_PREDICATE = "dst_predicate"
DST_OBJECT = "dst_object"
DST_LITERAL = "dst_literal"
DST_LITERAL_TYPE = "dst_literal_type"
DST_LITERAL_LANGUAGE = "dst_literal_language"

def get_connection(is_memory=False):
    if is_memory:
        conn = sqlite3.connect(DATABASE_NAME)
    else:
        conn = sqlite3.connect(DATABASE_NAME)
    conn.row_factory = sqlite3.Row        
    return conn
    
    
class DSOSqlite3DAO(DSODAO):
       
    def save(self, dsos):

        conn = get_connection()
        cursor = conn.cursor()
        
        # list of dso's to return
        dso_ids = []
        
        for dso in dsos:
            object_uri = dso.uri
            object_type = dso.type
            dso_id = dso.get_id()
                  
            # get version number
            version = dso.get_version()
            
            # if this dso has no id, then we assume it hasn't been created
            if not dso_id:
                # add dso
                cursor.execute("""INSERT INTO DSOI VALUES (null, (?), (?), (?))""",
                                [object_uri, object_type, True])
                
                # get primary key of last insert
                dso_id = cursor.lastrowid
            else:
                cursor.execute("""SELECT COUNT(*) FROM DSOI WHERE ID = (?)""", [dso_id])
                # check that the id does exist
                if not int(cursor.fetchone()[0]):
                    conn.close()
                    raise UpdateDAOException("DSO with id {0} does not exist".format(dso_id))
                
                # check that the save is using the latest version
                try:
                    self._check_version(dso_id, version, cursor)
                except ConcurrencyDAOException, e:
                    # close connection
                    conn.close
                    raise e
                            
            # add dso id to the list of returned dso's
            dso_ids.append(dso_id)
            
            # intialize version number if not set, otherwise increment
            if not version:
                version = 1
            else:
                version += 1
            
            # always create a new version    
            cursor.execute('INSERT INTO DSOV VALUES (null, (?), (?), (?))', 
                           [dso_id, version, dso.get_created_timestamp()])
            
            # get the key of the latest version
            dsov_id = cursor.lastrowid
        
            # add dst
            dsts = dso.get_dsts()
            for dst in dsts:
                sort_order = dst.sort_order
                dst_id = dst.get_id()
                subject = dst.subject
                predicate = dst.predicate
                object = dst.object
                literal = dst.literal
                literal_type = dst.literal_type
                literal_language = dst.literal_language
                # always create new dsts
                if dst_id is None:
                    cursor.execute('INSERT INTO DST VALUES (null, (?), (?), (?), (?), (?), (?), (?))',
                                   [sort_order, 
                                    subject, 
                                    predicate, 
                                    object,
                                    literal,
                                    literal_type,
                                    literal_language])
                    dst_id = cursor.lastrowid
                else:
                    # for dsts with ids, just check that they do actually exist
                    cursor.execute("""SELECT COUNT(*) FROM DST WHERE ID = (?)""", [dst_id])
                    if not int(cursor.fetchone()[0]):
                        conn.close()
                        raise UpdateDAOException("DST with id {0} does not exist".format(dst_id))
                # add mapping data
                cursor.execute('INSERT INTO DSOV_DST VALUES ((?), (?))', [dsov_id, dst_id])
        
        conn.commit()
        conn.close()
        
        dsos = self.find_by_ids(dso_ids)
        return dsos
        
    def find_by_id_and_version(self, id, version):

        query_string = self._get_standard_dso_select("""
                        where dsoi.id = DSOV.dsoi_id
                        and dsov.id = dsov_dst.dsov_id
                        and dst.id = dsov_dst.dst_id
                        and dsoi.id = (?)
                        and dsov.version = (?)""")
        conn = get_connection()
        cursor = conn.cursor()
        cursor.execute(query_string, [id, version])
        dso = self._create_dso(cursor)
        conn.close()
        if not dso:
            raise NotFoundDAOException("DSO with id {0} and version {1} does not exist".format(id, version))
        return dso
    
    def find_by_ids(self, ids):
        dsos = []
        for id in ids:
            query_string = self._get_standard_dso_select("""
                            where dsoi.id = DSOV.dsoi_id
                            and dsov.id = dsov_dst.dsov_id
                            and dst.id = dsov_dst.dst_id
                            and dsoi.id = (?)
                            and dsoi.active = 1
                            and dsov.version = (select max(version) from DSOV where dsoi_id = (?))""")
        
            conn = get_connection()
            cursor = conn.cursor()
            cursor.execute(query_string, [id, id])
            dso = self._create_dso(cursor)
            conn.close()
            if not dso:
                raise NotFoundDAOException("DSO with id {0} does not exist".format(id))
            else:
                dsos.append(dso)
        return dsos
    
    def find_by_URI_and_version(self, uri, version):
        query_string = self._get_standard_dso_select("""
                        where dsoi.id = DSOV.dsoi_id
                        and dsov.id = dsov_dst.dsov_id
                        and dst.id = dsov_dst.dst_id
                        and dsoi.uri = (?)
                        and dsov.version = (?)""")
        conn = get_connection()
        cursor = conn.cursor()
        cursor.execute(query_string, [uri, version])
        dso = self._create_dso(cursor)
        conn.close()
        if not dso:
            raise NotFoundDAOException("DSO with URI {0} and version {1} does not exist".format(uri, version))
        return dso
    
    def find_by_URIs(self, uris):
        dsos = []
        for uri in uris:
            query_string = self._get_standard_dso_select("""
                            where dsoi.id = dsov.dsoi_id
                            and dsov.id = dsov_dst.dsov_id
                            and dst.id = dsov_dst.dst_id
                            and dsoi.uri = (?)
                            and dsoi.active = 1
                            and dsov.version = (select max(version) from DSOV dsov2 where dsov2.dsoi_id = dsoi.id)""")
            conn = get_connection()
            cursor = conn.cursor()
            cursor.execute(query_string, [uri])
            dso = self._create_dso(cursor)
            conn.close()
            if not dso:
                raise NotFoundDAOException("DSO with URI {0} does not exist".format(id))
            else:
                dsos.append(dso)
        return dsos
    
    def find_versions(self, id):
        query_string = """
                        select dsov.version as {0}
                        from DSOI dsoi, DSOV dsov
                        where dsoi.id = DSOV.dsoi_id
                        and dsoi.id = (?)
                        """.format(DSO_VERSION)         
        conn = get_connection()
        cursor = conn.cursor()
        cursor.execute(query_string, [id])
        rows = cursor.fetchall()
        versions = []
        if rows:
            for row in rows:
                versions.append(row[DSO_VERSION])
        
        conn.close()
        if versions:
            return versions
        else:
            raise NotFoundDAOException("DSO with id {0} does not exist".format(id))
    
    def delete(self, dsos):
        """ Delete marks the dso as inactive """
        conn = get_connection()
        cursor = conn.cursor()
        delete_string_1 = """UPDATE DSOI SET ACTIVE = (?) WHERE ID = (?)"""
        for dso in dsos:
            try:
                self._check_version(dso.id, dso.version, cursor)
                cursor.execute(delete_string_1, [False, dso.get_id()])
            except ConcurrencyDAOException, e:
                conn.close
                raise e
        conn.commit()
        conn.close()
        
#    def remove(self, dsos):
#        conn = get_connection()
#        cursor = conn.cursor()
#        delete_string_1 = """delete from dsoi where dsoi.id = (?)"""
#        delete_string_2 = """delete from dst where dst.id = (?)"""        
#        for dso in dsos:
#            dsts = dso.get_dsts()
#            for dst in dsts:
#                cursor.execute(delete_string_2, [dst.get_id()])
#            cursor.execute(delete_string_1, [dso.get_id()])
#        conn.commit()
#        conn.close()

    def _get_standard_dso_select(self, where_conditions):
        query_string = """
                        select dsoi.id as {0}, dsoi.uri as {1}, dsoi.type as {2}, 
                        dsoi.active as {3}, dsov.version as {4}, dsov.created_timestamp as {5},
                        dst.id as {6}, dst.sort_order as {7}, dst.subject as {8}, 
                        dst.predicate as {9}, dst.object as {10}, dst.literal as {11}, 
                        dst.literal_type as {12}, dst.literal_language as {13}
                        from DSOI dsoi, DSOV dsov, DSOV_DST dsov_dst, DST dst
                        """.format(DSO_ID, DSO_URI, DSO_TYPE, DSO_ACTIVE, DSO_VERSION, DSO_CREATED_TIMESTAMP,
                                   DST_ID, DST_SORT_ORDER, DST_SUBJECT, DST_PREDICATE, DST_OBJECT,
                                   DST_LITERAL, DST_LITERAL_TYPE, DST_LITERAL_LANGUAGE)
        query_string += where_conditions
        return query_string        
    
    def _check_version(self, dso_id, dso_version, cursor):
        cursor.execute('SELECT MAX(VERSION) FROM DSOV WHERE DSOI_ID = (?)', [dso_id])
        latest_version = cursor.fetchone()[0]
        if latest_version and latest_version != dso_version:
            raise ConcurrencyDAOException("Latest version is {0}, the version used is {1}".format(latest_version, dso_version))
        
    def _create_dso(self, cursor):
        dso = None
        rows = cursor.fetchall()
        if rows:
            dsts = set([])
            for row in rows:
                dst = DST(id=row[DST_ID],
                          subject=row[DST_SUBJECT],
                          predicate=row[DST_PREDICATE],
                          object=row[DST_OBJECT],
                          literal=row[DST_LITERAL],
                          literal_type=row[DST_LITERAL_TYPE]
                          )
                dst.sort_order = row[DST_SORT_ORDER]
                dst.literal_language = row[DST_LITERAL_LANGUAGE]
                dsts.add(dst)
            row = rows[0]
            dso = DSO(id=row[DSO_ID], 
                      version=row[DSO_VERSION],
                      created_timestamp=row[DSO_CREATED_TIMESTAMP],
                      active=row[DSO_ACTIVE],
                      dsts=dsts)
            dso.uri = row[DSO_URI]
            dso.type = row[DSO_TYPE]
            return dso
        else:
            return None